﻿--HEADER Create UDS Schema; Create UDS tables; Copy iPerform.Idea table to UDS.Idea table and related tables


CREATE SCHEMA [UDS] AUTHORIZATION [dbo]
GO


--Create Question table
CREATE TABLE [UDS].[Question](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[TypeId] [tinyint] NOT NULL,
	[AnswerTypeId] [tinyint] NOT NULL,
	CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

--Create QuestionAnswerOption table
CREATE TABLE [UDS].[QuestionAnswerOption]
(
	[Id] INT IDENTITY(1,1) NOT NULL , 
    [Name] NVARCHAR(150) NOT NULL, 
    [QuestionId] INT NOT NULL,
	CONSTRAINT [PK_QuestionAnswerOption] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

ALTER TABLE [UDS].[QuestionAnswerOption]  ADD  CONSTRAINT [FK_QuestionAnswerOption_Question] FOREIGN KEY([QuestionId])
REFERENCES [UDS].[Question] ([Id])
GO

--Create Answer table
CREATE TABLE [UDS].[Answer]
(
	[Id] INT IDENTITY(1,1) NOT NULL , 
    [Value] NVARCHAR(MAX) NULL, 
    [DateTime] DATETIME2 NOT NULL, 
    [CreatedOn] DATETIME2 NOT NULL, 
    [UserId] INT NOT NULL, 
    [QuestionId] INT NOT NULL,
	CONSTRAINT [PK_Answer] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

ALTER TABLE [UDS].[Answer]  ADD  CONSTRAINT [FK_Answer_Question] FOREIGN KEY([QuestionId])
REFERENCES [UDS].[Question] ([Id])
GO

ALTER TABLE [UDS].[Answer] ADD  CONSTRAINT [FK_Answer_User] FOREIGN KEY([UserId])
REFERENCES [Membership].[User] ([Id])
GO

--Create IdeaAnswer table
CREATE TABLE [UDS].[IdeaAnswer]
(
	[Id] INT NOT NULL , 
    [IdeaId] UNIQUEIDENTIFIER NOT NULL, 
    [SecurityId] INT NOT NULL,
	CONSTRAINT [PK_IdeaAnswer] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

ALTER TABLE [UDS].[IdeaAnswer]  ADD  CONSTRAINT [FK_IdeaAnswer_Answer] FOREIGN KEY([Id])
REFERENCES [UDS].[Answer] ([Id])
GO

--Create Template table
CREATE TABLE [UDS].[Template]
(
	[Id] INT IDENTITY(1,1) NOT NULL , 
    [Name] NVARCHAR(150) NOT NULL,
	CONSTRAINT [PK_Template] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

--Create QuestionToTemplate table
CREATE TABLE [UDS].[QuestionToTemplate]
(
	[QuestionId] INT NOT NULL, 
    [TemplateId] INT NOT NULL,
	CONSTRAINT [PK_QuestionToTemplate] PRIMARY KEY CLUSTERED ([QuestionId] ASC, [TemplateId] ASC)
)

GO

ALTER TABLE [UDS].[QuestionToTemplate] ADD  CONSTRAINT [FK_QuestionToTemplate_Question] FOREIGN KEY([QuestionId])
REFERENCES [UDS].[Question] ([Id])
GO

ALTER TABLE [UDS].[QuestionToTemplate] ADD  CONSTRAINT [FK_QuestionToTemplate_Template] FOREIGN KEY([TemplateId])
REFERENCES [UDS].[Template] ([Id])
GO

--Create QuestionToUser Table
CREATE TABLE [UDS].[QuestionToUser]
(
	[QuestionId] INT NOT NULL, 
    [UserId] INT NOT NULL,
	CONSTRAINT [PK_QuestionToUser] PRIMARY KEY CLUSTERED ([QuestionId] ASC, [UserId] ASC)
)

GO

ALTER TABLE [UDS].[QuestionToUser] ADD  CONSTRAINT [FK_QuestionToUser_Question] FOREIGN KEY([QuestionId])
REFERENCES [UDS].[Question] ([Id])
GO

ALTER TABLE [UDS].[QuestionToUser]  ADD  CONSTRAINT [FK_QuestionToUser_User] FOREIGN KEY([UserId])
REFERENCES [Membership].[User] ([Id])
GO

--Create Idea table
CREATE TABLE [UDS].[Idea] (
    [Id]                     uniqueidentifier            NOT NULL,
    [Closed]                 BIT            NOT NULL,
    [FundBookEntityId]       INT            NOT NULL,
    [OwnerUserId]            INT            NULL,    
    [Name]                   NVARCHAR (150) COLLATE Latin1_General_CI_AS NOT NULL,
    [Type]                   TINYINT       NULL, 
    [CreatedDateTime] DATETIME2 NOT NULL, 
    [ClosedDateTime] DATETIME2 NULL, 
	CONSTRAINT [PK_Idea] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

--Create TemplateToIdea table
CREATE TABLE [UDS].[TemplateToIdea]
(
	[TemplateId] INT NOT NULL, 
    [IdeaId] UNIQUEIDENTIFIER NOT NULL,
	CONSTRAINT [PK_TemplateToIdea] PRIMARY KEY CLUSTERED ([TemplateId] ASC,	[IdeaId] ASC)
)

GO

ALTER TABLE [UDS].[TemplateToIdea] ADD  CONSTRAINT [FK_TemplateToIdea_Idea] FOREIGN KEY([IdeaId])
REFERENCES [UDS].[Idea] ([Id])
GO

ALTER TABLE [UDS].[TemplateToIdea] ADD  CONSTRAINT [FK_TemplateToIdea_Template] FOREIGN KEY([TemplateId])
REFERENCES [UDS].[Template] ([Id])
GO

--Create TradeToIdea
CREATE TABLE [UDS].[TradeToIdea]
(
	[TradeId] INT NOT NULL, 
    [IdeaId] UNIQUEIDENTIFIER NOT NULL, 
    [LinkerEntityCode] SMALLINT NOT NULL, 
    [Timestamp] DATETIME2 NOT NULL,
	CONSTRAINT [PK_TradeToIdea] PRIMARY KEY CLUSTERED ([TradeId] ASC, [IdeaId] ASC)
)

GO

ALTER TABLE [UDS].[TradeToIdea]  ADD  CONSTRAINT [FK_TradeToIdea_Idea] FOREIGN KEY([IdeaId])
REFERENCES [UDS].[Idea] ([Id])
GO


--Create SecurityToIdea
CREATE TABLE [UDS].[SecurityToIdea]
(
	[SecurityId] INT NOT NULL, 
    [IdeaId] UNIQUEIDENTIFIER NOT NULL,
	CONSTRAINT [PK_SecurityToIdea] PRIMARY KEY CLUSTERED ([SecurityId] ASC,	[IdeaId] ASC)
)

GO

ALTER TABLE [UDS].[SecurityToIdea] ADD  CONSTRAINT [FK_SecurityToIdea_Idea] FOREIGN KEY([IdeaId])
REFERENCES [UDS].[Idea] ([Id])
GO


--Copy iPerform.Idea table to UDS.Idea table
INSERT UDS.Idea ([Id], [Closed], [FundBookEntityId], [OwnerUserId], [Name], [Type], [CreatedDateTime], [ClosedDateTime])
SELECT [Id], [Closed], [FundBookEntityId], [OwnerUserId], [Name], [Type], [StartDate], [EndDate]
FROM iPerform.Idea

--Copy iPerform.IdeaToTrade to UDS.TradeToIdea
INSERT UDS.TradeToIdea (TradeId, IdeaId, LinkerEntityCode, [Timestamp])
SELECT TradeId, IdeaId, LinkerEntityCode, [Timestamp]
FROM iPerform.IdeaToTrade

--Copy iPerform.IdeaToSecurity to UDS.SecurityToIdea
INSERT UDS.SecurityToIdea (SecurityId, IdeaId)
SELECT SecurityId, IdeaId
FROM iPerform.IdeaToSecurity

--Create DiaryEntry table
CREATE TABLE [UDS].[DiaryEntry]
(
	[Id] INT NOT NULL , 
    [Text] NVARCHAR(MAX) NULL, 
    [LastModified] DATETIME2 NOT NULL,
	CONSTRAINT [PK_DiaryEntry] PRIMARY KEY CLUSTERED ([Id] ASC)
)

GO

ALTER TABLE [UDS].[DiaryEntry]  ADD  CONSTRAINT [FK_DiaryEntry_Answer] FOREIGN KEY([Id])
REFERENCES [UDS].[Answer] ([Id])
GO

--Create AnswerToIdea
CREATE TABLE [UDS].[AnswerToIdea]
(
	[AnswerId] INT NOT NULL , 
    [IdeaId] UNIQUEIDENTIFIER NOT NULL, 
    CONSTRAINT [PK_AnswerToIdea] PRIMARY KEY ([AnswerId], [IdeaId])
)

GO

ALTER TABLE [UDS].[AnswerToIdea] ADD  CONSTRAINT [FK_AnswerToIdea_Answer] FOREIGN KEY([AnswerId])
REFERENCES [UDS].[Answer] ([Id])
GO

ALTER TABLE [UDS].[AnswerToIdea]  ADD  CONSTRAINT [FK_AnswerToIdea_Idea] FOREIGN KEY([IdeaId])
REFERENCES [UDS].[Idea] ([Id])


